<?php
/**
 * 系统设置表结构迁移脚本
 * 从竖表改为横表
 */

// 数据库配置
$config = [
    'host' => '113.44.136.17',
    'port' => 3306,
    'username' => 'qiandao',
    'password' => 'NGbks3HWx5PhSxDX',
    'database' => 'qiandao',
    'charset' => 'utf8mb4',
];

try {
    // 连接数据库
    $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']};charset={$config['charset']}";
    $pdo = new PDO($dsn, $config['username'], $config['password']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "✓ 数据库连接成功\n";
    
    // 开始事务
    $pdo->beginTransaction();
    
    echo "\n=== 开始迁移 ===\n\n";
    
    // 1. 备份原表数据
    echo "1. 备份原表数据...\n";
    $pdo->exec("DROP TABLE IF EXISTS `checkin_setting_backup`");
    $pdo->exec("CREATE TABLE `checkin_setting_backup` LIKE `checkin_setting`");
    $pdo->exec("INSERT INTO `checkin_setting_backup` SELECT * FROM `checkin_setting`");
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM `checkin_setting_backup`");
    $count = $stmt->fetchColumn();
    echo "   ✓ 已备份 {$count} 条记录到 checkin_setting_backup\n\n";
    
    // 2. 删除旧表
    echo "2. 删除旧表...\n";
    $pdo->exec("DROP TABLE IF EXISTS `checkin_setting`");
    echo "   ✓ 旧表已删除\n\n";
    
    // 3. 创建新的横表结构
    echo "3. 创建新的横表结构...\n";
    $createTableSQL = "
    CREATE TABLE `checkin_setting` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '门店ID，0为全局配置（预留字段）',
      
      `unit_minutes` int(11) NOT NULL DEFAULT '10' COMMENT '每满N分钟记1分',
      `rounding` varchar(20) NOT NULL DEFAULT 'floor' COMMENT '积分取整方式：floor向下取整, ceil向上取整, round四舍五入',
      `timeout_minutes` int(11) NOT NULL DEFAULT '0' COMMENT '超时作废阈值(分钟)，0=不启用',
      `time_slots` text COMMENT '按时间段积分配置（JSON格式）',
      `points_expire_days` int(11) NOT NULL DEFAULT '0' COMMENT '积分有效期(天)，0为永久有效',
      
      `voice_start` tinyint(1) NOT NULL DEFAULT '1' COMMENT '打卡开始语音播报：1开启, 0关闭',
      `voice_end` tinyint(1) NOT NULL DEFAULT '1' COMMENT '打卡结束语音播报：1开启, 0关闭',
      
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `update_time` datetime DEFAULT NULL COMMENT '更新时间',
      
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_store_id` (`store_id`) COMMENT '每个门店只有一条配置记录'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统设置表（横表结构）'
    ";
    $pdo->exec($createTableSQL);
    echo "   ✓ 新表结构创建成功\n\n";
    
    // 4. 从备份表迁移数据
    echo "4. 迁移数据...\n";
    
    // 获取旧数据
    $stmt = $pdo->query("SELECT `key`, `value` FROM `checkin_setting_backup` WHERE `store_id` = 0");
    $oldData = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $oldData[$row['key']] = $row['value'];
    }
    
    echo "   从备份表读取到 " . count($oldData) . " 个配置项\n";
    
    // 准备新数据
    $newData = [
        'store_id' => 0,
        'unit_minutes' => isset($oldData['unit_minutes']) ? intval($oldData['unit_minutes']) : 10,
        'rounding' => isset($oldData['rounding']) ? $oldData['rounding'] : 'floor',
        'timeout_minutes' => isset($oldData['timeout_minutes']) ? intval($oldData['timeout_minutes']) : 0,
        'time_slots' => isset($oldData['time_slots']) ? $oldData['time_slots'] : '',
        'points_expire_days' => isset($oldData['points_expire_days']) ? intval($oldData['points_expire_days']) : 0,
        'voice_start' => isset($oldData['voice_start']) ? intval($oldData['voice_start']) : 1,
        'voice_end' => isset($oldData['voice_end']) ? intval($oldData['voice_end']) : 1,
        'create_time' => date('Y-m-d H:i:s'),
        'update_time' => date('Y-m-d H:i:s'),
    ];
    
    // 插入新数据
    $insertSQL = "
    INSERT INTO `checkin_setting` (
        `store_id`, `unit_minutes`, `rounding`, `timeout_minutes`, `time_slots`,
        `points_expire_days`, `voice_start`, `voice_end`, `create_time`, `update_time`
    ) VALUES (
        :store_id, :unit_minutes, :rounding, :timeout_minutes, :time_slots,
        :points_expire_days, :voice_start, :voice_end, :create_time, :update_time
    )
    ";
    
    $stmt = $pdo->prepare($insertSQL);
    $stmt->execute($newData);
    
    echo "   ✓ 数据迁移成功\n\n";
    
    // 5. 验证数据
    echo "5. 验证迁移结果...\n";
    $stmt = $pdo->query("SELECT * FROM `checkin_setting` WHERE `store_id` = 0");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($result) {
        echo "   ✓ 验证成功！迁移后的配置：\n";
        echo "   - 单位分钟: {$result['unit_minutes']}\n";
        echo "   - 取整方式: {$result['rounding']}\n";
        echo "   - 超时作废: {$result['timeout_minutes']}\n";
        echo "   - 积分有效期: {$result['points_expire_days']}\n";
        echo "   - 开始播报: {$result['voice_start']}\n";
        echo "   - 结束播报: {$result['voice_end']}\n";
        echo "   - 时间段配置: " . (empty($result['time_slots']) ? '(空)' : '已配置') . "\n";
    } else {
        throw new Exception("验证失败：未找到迁移后的数据");
    }
    
    // 提交事务
    $pdo->commit();
    
    echo "\n=== 迁移完成 ===\n\n";
    echo "✓ 所有操作已成功完成！\n";
    echo "✓ 原数据已备份到 checkin_setting_backup 表\n";
    echo "✓ 新表结构已创建并迁移数据\n\n";
    echo "提示：\n";
    echo "1. 请访问后台「系统设置」页面测试功能\n";
    echo "2. 确认无误后可删除备份表：DROP TABLE checkin_setting_backup;\n";
    
} catch (Exception $e) {
    // 回滚事务
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    
    echo "\n✗ 迁移失败：" . $e->getMessage() . "\n";
    echo "\n已回滚所有更改，数据库保持原状。\n";
    exit(1);
}

